package com.shtel.paas.monitorcenter.autodiscover.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.UUID;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Component;

//import org.junit.Test;

@Component
@PropertySource("classpath:/grafana_address.properties")
public class AutoFind {
	
//	public static void main(String args[]){
//		new AutoFind().autoFindInsert("10.145.242.777");
//	}
	
	@Value("${grafana_address}")
	private String grafanaAddress;
	
	public void autoFindInsert(String this_ip){
		Connection conn = null;
		Statement stat = null;
		PreparedStatement prep = null;
		
		//添加的ip
		String ip = this_ip;
		try {
			// 连接SQLite的JDBC
			Class.forName("org.sqlite.JDBC");

			String url="jdbc:sqlite://"+grafanaAddress;
			conn = DriverManager.getConnection(url);//jdbc:sqlite://C:/Users/Pactera/Desktop/grafana.db
			conn.setAutoCommit(false);
			stat = conn.createStatement();
//			String data = "{\"hello\"}";
			UUID uuid = UUID.randomUUID();
			String uid  = uuid.toString().substring(0, 5)+uuid.toString().substring(32);
			
			String data = "{\"annotations\":{\"list\":[{\"builtIn\":1,\"datasource\":\"-- Grafana --\",\"enable\":true,\"hide\":true,\"iconColor\":\"rgba(0, 211, 255, 1)\",\"name\":\"Annotations \u0026 Alerts\",\"type\":\"dashboard\"}]},\"editable\":true,\"gnetId\":null,\"graphTooltip\":1,\"id\":17,\"links\":[{\"icon\":\"info\",\"tags\":[],\"targetBlank\":true,\"title\":\"Grafana Docs\",\"tooltip\":\"\",\"type\":\"link\",\"url\":\"http://docs.grafana.org/\"},{\"icon\":\"info\",\"tags\":[],\"targetBlank\":true,\"title\":\"Prometheus Docs\",\"type\":\"link\",\"url\":\"http://prometheus.io/docs/introduction/overview/\"}],\"panels\":[{\"aliasColors\":{\"{instance=\""+ip+":9100\",mode=\"idle\"}\":\"#e0752d\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"prometheus\",\"fill\":1,\"gridPos\":{\"h\":6,\"w\":9,\"x\":0,\"y\":0},\"id\":43,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"expr\":\"100-((node_memory_MemFree+node_memory_Cached+node_memory_Buffers)/node_memory_MemTotal{instance=\""+ip+":9100\"})*100\",\"format\":\"time_series\",\"hide\":false,\"instant\":false,\"intervalFactor\":1,\"legendFormat\":\"使用率\",\"refId\":\"A\"}],\"thresholds\":[{\"colorMode\":\"critical\",\"fill\":true,\"line\":true,\"op\":\"lt\",\"value\":6.3}],\"timeFrom\":null,\"timeShift\":null,\"title\":\"主机内存当前使用率54321\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}]},{\"aliasColors\":{\"系统百分比\":\"#cca300\"},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"prometheus\",\"fill\":1,\"gridPos\":{\"h\":6,\"w\":9,\"x\":9,\"y\":0},\"id\":45,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"expr\":\"avg by (instance, mode)(clamp_max(irate(node_cpu{mode=\"system\",instance=\""+ip+":9100\"}[5m]),1))*100\",\"format\":\"time_series\",\"intervalFactor\":1,\"legendFormat\":\"系统百分比\",\"refId\":\"A\"}],\"thresholds\":[{\"colorMode\":\"ok\",\"fill\":false,\"line\":true,\"op\":\"lt\",\"value\":6.1},{\"colorMode\":\"warning\",\"fill\":false,\"line\":true,\"op\":\"lt\",\"value\":6.2},{\"colorMode\":\"critical\",\"fill\":false,\"line\":true,\"op\":\"gt\",\"value\":6.33}],\"timeFrom\":null,\"timeShift\":null,\"title\":\"CPU时间：系统百分比\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}]},{\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"prometheus\",\"fill\":1,\"gridPos\":{\"h\":6,\"w\":6,\"x\":18,\"y\":0},\"id\":47,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"expr\":\"node_memory_Cached{instance=\""+ip+":9100\"}/1024\",\"format\":\"time_series\",\"intervalFactor\":1,\"legendFormat\":\"占用大小\",\"refId\":\"A\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"主机缓存占用大小\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}]},{\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"prometheus\",\"fill\":1,\"gridPos\":{\"h\":6,\"w\":9,\"x\":0,\"y\":6},\"id\":51,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"expr\":\"rate(node_disk_bytes_written{instance=\""+ip+":9100\"}[5m])\",\"format\":\"time_series\",\"intervalFactor\":1,\"refId\":\"A\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"磁盘当前每秒磁盘写请求\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}]},{\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"prometheus\",\"fill\":1,\"gridPos\":{\"h\":6,\"w\":9,\"x\":9,\"y\":6},\"id\":49,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"expr\":\"rate(node_disk_bytes_read{instance=\""+ip+":9100\"}[5m])\",\"format\":\"time_series\",\"intervalFactor\":1,\"legendFormat\":\"\",\"refId\":\"A\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"磁盘当前每秒磁盘读请求\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}]},{\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"prometheus\",\"fill\":1,\"gridPos\":{\"h\":6,\"w\":6,\"x\":18,\"y\":6},\"id\":57,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"expr\":\"node_network_transmit_drop/node_network_transmit_bytes{instance=\""+ip+":9100\"}\",\"format\":\"time_series\",\"intervalFactor\":1,\"refId\":\"A\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"端口丢包率\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}]},{\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"prometheus\",\"fill\":1,\"gridPos\":{\"h\":6,\"w\":9,\"x\":0,\"y\":12},\"id\":53,\"legend\":{\"alignAsTable\":false,\"avg\":false,\"current\":false,\"hideEmpty\":false,\"hideZero\":false,\"max\":false,\"min\":false,\"rightSide\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"expr\":\"rate(node_network_receive_bytes{instance=\""+ip+":9100\"}[5m])/1024\",\"format\":\"time_series\",\"instant\":false,\"interval\":\"\",\"intervalFactor\":1,\"legendFormat\":\"\",\"refId\":\"A\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"网络当前每秒读请求数据量\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"transparent\":false,\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}]},{\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"prometheus\",\"fill\":1,\"gridPos\":{\"h\":6,\"w\":9,\"x\":9,\"y\":12},\"id\":55,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"expr\":\"rate(node_network_transmit_bytes{instance=\""+ip+":9100\"}[5m])/1024\",\"format\":\"time_series\",\"intervalFactor\":1,\"refId\":\"A\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"网络当前每秒写请求数据量\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}]},{\"aliasColors\":{},\"bars\":false,\"dashLength\":10,\"dashes\":false,\"datasource\":\"prometheus\",\"fill\":1,\"gridPos\":{\"h\":6,\"w\":6,\"x\":18,\"y\":12},\"id\":59,\"legend\":{\"avg\":false,\"current\":false,\"max\":false,\"min\":false,\"show\":true,\"total\":false,\"values\":false},\"lines\":true,\"linewidth\":1,\"links\":[],\"nullPointMode\":\"null\",\"percentage\":false,\"pointradius\":5,\"points\":false,\"renderer\":\"flot\",\"seriesOverrides\":[],\"spaceLength\":10,\"stack\":false,\"steppedLine\":false,\"targets\":[{\"expr\":\"node_memory_SwapCached/node_memory_SwapTotal{instance=\""+ip+":9100\"}\",\"format\":\"time_series\",\"intervalFactor\":1,\"legendFormat\":\"百分比\",\"refId\":\"A\"}],\"thresholds\":[],\"timeFrom\":null,\"timeShift\":null,\"title\":\"交换区使用百分比\",\"tooltip\":{\"shared\":true,\"sort\":0,\"value_type\":\"individual\"},\"type\":\"graph\",\"xaxis\":{\"buckets\":null,\"mode\":\"time\",\"name\":null,\"show\":true,\"values\":[]},\"yaxes\":[{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true},{\"format\":\"short\",\"label\":null,\"logBase\":1,\"max\":null,\"min\":null,\"show\":true}]}],\"refresh\":\"1m\",\"revision\":\"1.0\",\"schemaVersion\":16,\"style\":\"dark\",\"tags\":[\"prometheus\"],\"templating\":{\"list\":[]},\"time\":{\"from\":\"now-1h\",\"to\":\"now\"},\"timepicker\":{\"now\":true,\"refresh_intervals\":[\"5s\",\"10s\",\"30s\",\"1m\",\"5m\",\"15m\",\"30m\",\"1h\",\"2h\",\"1d\"],\"time_options\":[\"5m\",\"15m\",\"1h\",\"6h\",\"12h\",\"24h\",\"2d\",\"7d\",\"30d\"]},\"timezone\":\"browser\",\"title\":\"统一监控—"+ip+"\",\"uid\":\""+uid+"\",\"version\":8}";
			String sql = "INSERT INTO dashboard (version,slug,title,data,org_id,created,updated,updated_by,created_by,gnet_id,folder_id,is_folder,has_acl,uid) " +
	                   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );";
			prep = conn.prepareStatement(sql);
			prep.setInt(1, 2); //version
			prep.setString(2, "tong-jian-kong-"+ip+""); //slug
			prep.setString(3, "统一监控—"+ip); //title
			prep.setString(4, data); //data
			prep.setInt(5, 1); //org_id
			prep.setString(6, new Date().toString()); //created
			prep.setString(7, new Date().toString()); //updated
			prep.setInt(8, 1); //updated_by
			prep.setInt(9, 1); //created_by
			prep.setInt(10, 0); //gnet_id
			prep.setInt(11, 27); //folder_id
			prep.setInt(12, 0); //is_folder
			prep.setInt(13, 0); //has_acl
			prep.setString(14, uid); //uid
			
			prep.addBatch();
			prep.executeBatch();
			//ResultSet rs = stat.executeQuery("select * from dashboard;"); // 查询数据
			System.out.println("执行成功!");
			

		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				stat.close();
				conn.commit();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

}
